/*
 *  Copyright 2008 The MITRE Corporation (http://www.mitre.org/). All Rights Reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.mitre.mrald.util;

import org.mitre.mrald.util.MetaData;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import org.mitre.lattice.query.LatticeQuery;
import org.mitre.mrald.control.MsgObject;
import org.mitre.mrald.control.WorkflowStepException;

/**
 *  Description of the Class
 *
 *@author     ghamilton
 *@created    Jan 20th , 2005
 */
public class LatticeHtmlKeywordSearch extends KeywordSearch
{

	private User user = null;
	private int lineLimit = -1;
	private int sizeLimit = -1;
    private MsgObject msg;

	/**
	 *  Description of the Method
	 *
	 *@param  msg                        Description of the Parameter
	 *@exception  WorkflowStepException  Description of the Exception
	 */
	public void execute(MsgObject msg) throws WorkflowStepException
	{
        this.msg = msg;
		try
		{

			String[] searchParams = msg.getValue("term");
            datasource = msg.getValue(FormTags.DATASOURCE_TAG)[0];

			String lineLimitStr = msg.getValue("lineLimit")[0];
			if ((lineLimitStr != null) && (!lineLimitStr.equals("")) )
				lineLimit = Integer.valueOf(lineLimitStr).intValue();

			String sizeLimitStr = msg.getValue("sizeLimit")[0];
			if ((sizeLimitStr != null) && (!sizeLimitStr.equals("")) )
				sizeLimit = Integer.valueOf(sizeLimitStr).intValue();

			java.io.PrintWriter out = msg.getOutPrintWriter();

			search(searchParams, out);
			out.write("\n");
			out.close();
		} catch (MraldException me)
		{
			throw new WorkflowStepException(me);
		}
	}


	/**
	 *  Constructor for the AllTablesListTag object
	 */
	public LatticeHtmlKeywordSearch()
	{
		super();
	}

	public void setUser(User thisUser)
	{
		this.user = thisUser;
	}

	public void setLineLimit(String lineLimitStr)
	{
		if ((lineLimitStr != null) && (!lineLimitStr.equals("")) )
				lineLimit = Integer.valueOf(lineLimitStr).intValue();

	}

	public void setSizeLimit(String sizeLimitStr)
	{
		if ((sizeLimitStr != null) && (!sizeLimitStr.equals("")) )
				sizeLimit = Integer.valueOf(sizeLimitStr).intValue();

	}


	/**
	 *  Description of the Method
	 *
	 *@param  searchParams        Description of the Parameter
	 *@return                     Description of the Return Value
	 *@exception  MraldException  Description of the Exception
	 */
	public void search(String[] searchParams, Writer out) throws MraldException
	{
		try
		{
		    //			LatticeQuery latQuery = new LatticeQuery();
			MraldOutFile.logToFile(Config.getProperty("LOGFILE"), "LatticeHtmlKeywords : datasource " + datasource );
			MraldOutFile.logToFile(Config.getProperty("LOGFILE"), "LatticeHtmlKeywords : msg " + msg );

			//Connection conn = new MraldConnection( datasource, msg ).getConnection();
			Connection conn = new MraldConnection( datasource ).getConnection();
			Statement stmt = conn.createStatement();
			StringBuffer strRtn = new StringBuffer();

			if (searchParams.length < 1)
			{
				out.write("\nYou must enter a search criteria.\n");
				return;
			}
			//Must enter a search parameter

			/*
			 *  all tables and their metadata should now be in memory
			 */
            DBMetaData md = MetaData.getDbMetaData( datasource );
			Collection tableMetaData = md.getAllTableMetaData();
			TableMetaData tableInfo;
			Iterator iter = tableMetaData.iterator();
			StringBuffer strOut = new StringBuffer();
			//Loop through the tables and find the Keyword in Table Names
			String titleStr = "<br/><br/><table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" bgcolor=\"#ffffff\"><tr><td class=\"bord\"><table cellspacing=\"1\" cellpadding=\"1\" border=\"0\" width=\"100%\">";
			titleStr = titleStr + "<tr><th colspan=\"2\" bgcolor=\"#ccccff\">List of Table Names Containing Keyword <i>" + searchParams[0] +"</i></th></tr>";
			boolean tableFound = false;
			while (iter.hasNext())
			{
				tableInfo = (TableMetaData) iter.next();


				if (tableInfo.getName().indexOf(searchParams[0]) > -1)
				{
					tableFound = true;

					strOut.append( "<tr><td align=\"center\"><b>" + tableInfo.getName() +"</b></td></tr>");
				}
			}
			String end = "</table></td></tr></table>";

			if (tableFound) strRtn.append( titleStr + strOut.toString() + end);
			out.write(strRtn.toString());
			strOut = new StringBuffer();
			strRtn = new StringBuffer();

			//Check if any columns have the string
			boolean colFound = false;
			iter = tableMetaData.iterator();
			 titleStr = "<br/><br/><table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" bgcolor=\"#ffffff\"><tr><td class=\"bord\"><table cellspacing=\"1\" cellpadding=\"1\" border=\"0\" width=\"100%\">";
			titleStr = titleStr + "<tr><th colspan=\"2\" bgcolor=\"#ccccff\">List of Column Names Containing Keyword <i>" + searchParams[0] +"</i></th></tr><tr><th>Table</th><th>Column</th></tr>";
			strOut = new StringBuffer();
			while (iter.hasNext())
			{
				tableInfo = (TableMetaData) iter.next();

				ArrayList fieldNames = (ArrayList) tableInfo.getColumnNames();
				Iterator iterFields = fieldNames.iterator();

				while(iterFields.hasNext())
				{
					String field = (String) iterFields.next();

					if (field.indexOf(searchParams[0]) > -1)
					{
						colFound = true;

						strOut.append( "<tr><td align=\"center\"><b>" + tableInfo.getName() +"</b></td><td>" + field + "</td></tr>");
					}
				}
			}


			if (colFound) strRtn.append( titleStr + strOut.toString() + end);
			out.write(strRtn.toString());

			strOut = new StringBuffer();
			strRtn= new StringBuffer();

			iter = tableMetaData.iterator();
//			int orderNo = 1;
			/*
			 *  for each table . . .
			 */
			ResultSet rs=null;
			while (iter.hasNext())
			{
				titleStr = "<br/><br/><table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" bgcolor=\"#ffffff\"><tr><td class=\"bord\"><table cellspacing=\"1\" cellpadding=\"1\" border=\"0\" width=\"100%\">";
				titleStr = titleStr + "<tr><th colspan=\"<:colspan:>\" bgcolor=\"#ccccff\"><b><:Title:></b></th></tr>";

				StringBuffer header = new StringBuffer("<tr>");
				tableInfo = (TableMetaData) iter.next();

				ArrayList fieldNames = (ArrayList) tableInfo.getColumnNames();
				Iterator iter2 = fieldNames.iterator();
				/*
				 *  and for each field, output a row of options
				 */
				String field;
				String whereClause = " ( ";
				//Cannot do a Select * as the table name needs to be prepended onto the columnName in case of two
				//columns with the same name on different tables
				String selectClause = " select  ";


				String tableName = tableInfo.getName();

				if ( tableName.length() > 0 && tableName.charAt( 0 ) != '"'  && tableName.indexOf(' ') > -1 )
				{

					tableName = "\"" + tableName + "\"";
				}

				while (iter2.hasNext())
				{
					field = (String) iter2.next();

					Integer type = tableInfo.getFieldType(field);

					header.append("<td><h2>" + field + "</h2></td>");


					if ( field.length() > 0 && field.indexOf( FormTags.TABLE_TAG ) > -1 && field.charAt( 0 ) != '"'  && field.indexOf(' ') > -1 )
					{
						field = "\"" + field+ "\"";
					}

					if (!FBUtils.isNumberType(type) && !FBUtils.isDateType(type) && !FBUtils.isBooleanType(type) && !FBUtils.isBinaryType(type))
					{
						whereClause += tableName+ "." + field + " like '%" + searchParams[0] + "%' OR ";
					}
					selectClause +=  tableName + "." + field + ",";

				}
				if (whereClause.equals(""))
				{
					continue;
				}
				selectClause = selectClause.substring(0, selectClause.length() - 1) + " from ";
				whereClause += "1=0 ) ";

				String query = selectClause + tableName+ " where " + whereClause;

				query = LatticeQuery.adaptQuery(query, user);

				//MraldOutFile.logToFile(Config.getProperty("LOGFILE"), "LatticeHtmlKeywordSearch : search . New query : " + query );

				if (query == null)
				{
					MraldException se = new MraldException("You must be logged in to return the results from the Keyword Search");
					se.fillInStackTrace();
					throw se;
				}

				rs = stmt.executeQuery(query);

				String rows = LinkUtils.getLinkTableHtml(md, tableInfo, rs,lineLimit, sizeLimit);

				//String rows = LinkUtils.getLinkTableHtml(out, md, allPkLinks, tableInfos, rs, lineLimitSize, mbLimitSize);

				if (!rows.equals(""))
				{
					strOut = new StringBuffer();
					header.append("</tr>");

					strOut.append(rows);
					strOut.append("</table></td></tr></table>");
					titleStr = titleStr.replaceAll("<:Title:>", tableInfo.getName());
					titleStr = titleStr.replaceAll("<:colspan:>", fieldNames.size() + "");
					strRtn.append(titleStr + header.toString() + strOut.toString());

					out.write(strRtn.toString());
					strOut = new StringBuffer();
					strRtn = new StringBuffer();

				}

			}
			if (rs !=null) rs.close();
			if (stmt != null ) stmt.close();
			if (conn != null) conn.close();
			out.write(strRtn.toString());

		} catch (SQLException e)
		{
			MraldException se = new MraldException(e);
			throw se;
		}
		catch (java.io.IOException e)
		{
			MraldException se = new MraldException(e);
			throw se;
		}


	}
}

